﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

namespace DataAccess.SqlServer
{
    public class SqlServerHopDongNhanVien: IHopDongNhanVien
    {
        public List<HopDongNhanVien> GetListHopDongNhanVien(QuanLyHopDong quanLyHopDong)
        {
            string sql = @" Select a.Oid, a.SoHopDong, a.NgayKy, a.ChucVuNguoiKy, a.NguoiKy, a.NguoiLaoDong, a.DiaDiemLamViec, a.ChucDanhChuyenMon, a.LoaiHopDong,
                                   b.QuanLyHopDong, b.HinhThucHopDong, b.TuNgay, b.DenNgay
                            From HopDong a inner join HopDongNhanVien b on a.Oid = b.Oid
                            Where QuanLyHopDong = @QuanLyHopDong";

            SqlParameter param = new SqlParameter("@QuanLyHopDong", quanLyHopDong.Oid);

            DataTable dt = DataProvider.GetDataTable(sql, param);
            List<HopDongNhanVien> list = new List<HopDongNhanVien>();

            foreach (DataRow row in dt.Rows)
            {
                HopDongNhanVien hopDongNhanVien = new HopDongNhanVien();

                hopDongNhanVien.Oid = int.Parse(row["Oid"].ToString());
                hopDongNhanVien.SoHopDong = row["SoHopDong"].ToString();
                hopDongNhanVien.NgayKy = DateTime.Parse(row["NgayKy"].ToString());
                int id;
                if (int.TryParse(row["ChucVuNguoiKy"].ToString(), out id))
                {
                    SqlServerChucVu sqlServerChucVu = new SqlServerChucVu();
                    hopDongNhanVien.ChucVuNguoiKy = sqlServerChucVu.GetChucVu(id);
                }
                if (int.TryParse(row["NguoiKy"].ToString(), out id))
                {
                    SqlServerNhanVien sqlServerNhanVien = new SqlServerNhanVien();
                    hopDongNhanVien.NguoiKy = sqlServerNhanVien.GetNhanVien(id);
                }
                if (int.TryParse(row["NguoiLaoDong"].ToString(), out id))
                {
                    hopDongNhanVien.NguoiLaoDong = HoSoFactory.GetHoSo(id);
                }
                if (int.TryParse(row["DiaDiemLamViec"].ToString(), out id))
                {
                    SqlServerBoPhan sqlServerBoPhan = new SqlServerBoPhan();
                    hopDongNhanVien.DiaDiemLamViec = sqlServerBoPhan.GetBoPhan(id);
                }
                hopDongNhanVien.ChucDanhChuyenMon = row["ChucDanhChuyenMon"].ToString();
                hopDongNhanVien.LoaiHopDong = row["LoaiHopDong"].ToString();

                hopDongNhanVien.QuanLyHopDong = quanLyHopDong;

                if (int.TryParse(row["HinhThucHopDong"].ToString(), out id))
                {
                    SqlServerHinhThucHopDong sqlServerHinhThucHopDong = new SqlServerHinhThucHopDong();
                    hopDongNhanVien.HinhThucHopDong = sqlServerHinhThucHopDong.GetHinhThucHopDong(id);
                }
                hopDongNhanVien.TuNgay = DateTime.Parse(row["TuNgay"].ToString());
                hopDongNhanVien.DenNgay = DateTime.Parse(row["DenNgay"].ToString());

                list.Add(hopDongNhanVien);
            }
            return list;
        }

        public int Insert(HopDongNhanVien hopDongNhanVien)
        {
            SqlServerHopDong sqlServerHopDong = new SqlServerHopDong();
            int oid = sqlServerHopDong.Insert((HopDong)hopDongNhanVien);

            string sql = @" Insert into HopDongNhanVien(Oid, QuanLyHopDong, HinhThucHopDong, TuNgay, DenNgay)
                            Values(@Oid, @QuanLyHopDong, @HinhThucHopDong, @TuNgay, @DenNgay)";

            SqlParameter[] param = {
                                       new SqlParameter("@Oid", oid),
                                       new SqlParameter("@QuanLyHopDong", hopDongNhanVien.QuanLyHopDong.Oid),
                                       new SqlParameter("@HinhThucHopDong", hopDongNhanVien.HinhThucHopDong.Oid),
                                       new SqlParameter("@TuNgay", hopDongNhanVien.TuNgay),
                                       new SqlParameter("@DenNgay", hopDongNhanVien.DenNgay)
                                   };
            DataProvider.Insert(sql, param);
            return oid;
        }

        public int Update(HopDongNhanVien hopDongNhanVien)
        {
            SqlServerHopDong sqlServerHopDong = new SqlServerHopDong();
            int temp = sqlServerHopDong.Update((HopDong)hopDongNhanVien);

            string sql = @" Update HopDongNhanVien
                            Set 
                                QuanLyHopDong = @QuanLyHopDong, 
                                HinhThucHopDong = @HinhThucHopDong, 
                                TuNgay = @TuNgay, 
                                DenNgay = @DenNgay
                            Where Oid = @Oid";

            SqlParameter[] param = {
                                       new SqlParameter("@Oid", hopDongNhanVien.Oid),
                                       new SqlParameter("@QuanLyHopDong", hopDongNhanVien.QuanLyHopDong),
                                       new SqlParameter("@HinhThucHopDong", hopDongNhanVien.HinhThucHopDong),
                                       new SqlParameter("@TuNgay", hopDongNhanVien.TuNgay),
                                       new SqlParameter("@DenNgay", hopDongNhanVien.DenNgay)
                                   };
            return DataProvider.Update(sql, param);
        }

        public int Delete(HopDongNhanVien hopDongNhanVien)
        {
            string sql = @" Delete From HopDongNhanVien Where Oid = @Oid";

            SqlParameter param = new SqlParameter("@Oid", hopDongNhanVien.Oid);
            int temp = DataProvider.Update(sql, param);

            if (temp > 0)
            {
                SqlServerHopDong sqlServerHopDong = new SqlServerHopDong();
                temp = sqlServerHopDong.Delete((HopDong)hopDongNhanVien);

                if (temp > 0)
                {
                    ThamChieuDuLieu thamChieuDuLieu = new ThamChieuDuLieu();
                    thamChieuDuLieu.GiaTri = hopDongNhanVien.Oid;

                    SqlServerThamChieuDuLieu sqlServerThamChieuDuLieu = new SqlServerThamChieuDuLieu();
                    temp = sqlServerThamChieuDuLieu.Delete(thamChieuDuLieu);

                }
            }
            return temp;
        }


        public int Save(HopDongNhanVien obj)
        {
            throw new NotImplementedException();
        }
    }
}
